package com.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import com.pojo.*;
import com.util.*;

public class CommentDao {

	public int tabCount;
	private String tableName = "t_comment";
	private String tableMainKey = "commentId";
	private Connection conn = null;
	private java.sql.ResultSet rs = null;
	java.sql.PreparedStatement pstmt = null;
	
	public CommentDao() {
		getCount("");
	}

	public boolean updateCommentVeriryResult(int commentId, int verifyResult) {
		if (conn == null)
			conn = DbUtil.getConnection();
		boolean flag = false;
		try {
			pstmt = conn.prepareStatement("update " + tableName
					+ " set verifyResult=? where " + tableMainKey + "=?");
			pstmt.setInt(1, verifyResult);
			pstmt.setInt(2, commentId);
			pstmt.execute();
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}

	public int addComment(CommentPojo pojo) {
		if (conn == null)
			conn = DbUtil.getConnection();
		int flag = -1;
		try {

			String sql = "insert into "
					+ tableName
					+ "(caseId,guestName,guestPhone,guestIp,commentDate,"
					+ "content,verifyResult) "
					+ "values(?,?,?,?,?,?,?);";
			System.out.println(sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, pojo.getCaseId());
			pstmt.setString(2, pojo.getGuestName());
			pstmt.setString(3, pojo.getGuestPhone());
			pstmt.setString(4, pojo.getGuestIp());
			pstmt.setTimestamp(5,pojo.getCommentDate());
			pstmt.setString(6, pojo.getContent());
			pstmt.setInt(7, pojo.getVerifyResult());
			pstmt.execute();
			flag = 1;
		} catch (Exception e) {
			Log.write(e);
		}
		return flag;
	}

	public List<CommentPojo> loadComment(int start, int number, String sWhere) {
		if (conn == null)
			conn = DbUtil.getConnection();
		List<CommentPojo> list = new ArrayList<CommentPojo>();
		try {
			String sql = "select distinct * from " + tableName + " " + sWhere
					+ " order by commentDate desc limit " + number * (start - 1)
					+ "," + number + ";";
			Log.write(sql);
			java.sql.PreparedStatement pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			CommentPojo pojo;
			while (rs.next()) {
				pojo = new CommentPojo();
				pojo.setCommentId(rs.getInt("CommentId"));
				pojo.setCaseId(rs.getInt("caseId"));
				pojo.setGuestName(rs.getString("guestName"));
				pojo.setGuestPhone(rs.getString("guestPhone"));
				pojo.setGuestIp(rs.getString("guestIp"));
				pojo.setCommentDate(rs.getTimestamp("commentDate"));
				pojo.setContent(rs.getString("content"));
				pojo.setVerifyResult(rs.getInt("verifyResult"));
				list.add(pojo);
			}
			getCount(sWhere);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return list;
	}

	public int deleteComment(String sWhere) {
		int result = -1;
		try {
			if (conn == null)
				conn = DbUtil.getConnection();
			pstmt = conn.prepareStatement("delete from " + tableName + sWhere);
			pstmt.execute();
			result = 0;
		} catch (Exception ex) {
			Log.write(ex);
		}
		return result;
	}
	
	private void getCount(String sWhere) {
		tabCount = 0;
		try {
			if (conn == null)
				conn = DbUtil.getConnection();
			pstmt = conn.prepareStatement("select count(*) as count from "
					+ tableName + " " + sWhere);
			rs = pstmt.executeQuery();
			if (rs.next())
				tabCount = rs.getInt("count");
		} catch (Exception ex) {
			ex.fillInStackTrace();
		}
	}

	// 关闭数据库连接
	public void closeAll() {
		DbUtil.close(pstmt, rs, conn);
	}
}
